﻿CREATE PROCEDURE [util].[GetLongRunningJobs]

AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int
	SET @RC = 0

    DECLARE @AllJobsFlag int
	SET @AllJobsFlag = 1
	
	CREATE TABLE #XP_RESULTS (
		[job_id] uniqueidentifier NOT NULL
		,[last_run_date] int NOT NULL
		,[last_run_time] int NOT NULL
		,[next_run_date] int NOT NULL
		,[next_run_time] int NOT NULL
		,[next_run_schedule_id] int NOT NULL
		,[requested_to_run] int NOT NULL
		,[request_source] int NOT NULL
		,[request_source_id] sysname COLLATE database_default NULL
		,[running] int NOT NULL
		,[current_step] int NOT NULL
		,[current_retry_attempt] int NOT NULL
		,[job_state] int NOT NULL
	)
	INSERT #XP_RESULTS
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @AllJobsFlag ,N'AllJobs'
	IF(@@ERROR <> 0) GOTO ERROR_HANDLER
	
	DECLARE @RunningStatus int
	SET @RunningStatus = 1

	SELECT
		job.[name] AS [JobName]
		,util.GetDateFromInt([last_run_date],[last_run_time]) AS [LastRuntime]
		,util.GetDateFromInt([next_run_date],[next_run_time]) AS [NextRuntime]
		,util.GetDurationFromDatePart(
			DATEDIFF(mi
				,util.GetDateFromInt([next_run_date],[next_run_time])
				,GETDATE()
			)
		,'') AS [Duration]
		,GETDATE() AS [CurrentTime]
		,res.[request_source_id]
	FROM #XP_RESULTS AS res
	INNER JOIN [msdb].dbo.sysjobs AS job
		ON (job.[job_id] = res.[job_id])
	WHERE  (res.[running] = @RunningStatus)
		AND(
				DATEDIFF(mi
					,util.GetDateFromInt([last_run_date],[last_run_time])
					,util.GetDateFromInt([next_run_date],[next_run_time])
				) < 
				DATEDIFF(mi
					,util.GetDateFromInt([next_run_date],[next_run_time])
					,GETDATE()
				)
			)

	GOTO EXIT_PROC

ERROR_HANDLER:
	SET @RC = 1

EXIT_PROC:
	IF(OBJECT_ID(N'tempdb.dbo.#XP_RESULTS') IS NOT NULL)
		DROP TABLE #XP_RESULTS
	RETURN(@RC)
END